Skip Headers

Oracle® Data Provider for .NET Developer's Guide 10g Release 2 (10.2) Part Number B14307-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Introducing .NET Stored Procedure Execution Using ODP.NET

Oracle Data Provider for .NET classes and APIs provide data access to the Oracle Database from a .NET client application and from .NET stored procedures and functions.

However, some limitations and restrictions exist when Oracle Data Provider for .NET is used within a .NET stored procedure. These are discussed in the next section.

The following is a simple .NET stored procedure example.

    using System;
    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;
    public class CLRLibrary1
    {
        // .NET Stored Function returning the DEPTNO of the employee whose
        // EMPNO is 'empno'
        public static uint GetDeptNo(uint empno)
        {
            uint deptno = 0;

            // Check for context connection
            OracleConnection conn = new OracleConnection();
            if( OracleConnection.IsAvailable == true )
            {
                conn.ConnectionString = "context connection=true";
            }
            else
            {
                //set connection string for a normal client connection
                conn.ConnectionString = "user id=scott;password=tiger;" +
                "data source=oracle";
            }

            conn.Open();
            // Create and execute a command
            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT DEPTNO FROM EMP WHERE EMPNO = :1";
            cmd.Parameters.Add(":1",OracleDbType.Int32,empno,
                System.Data.ParameterDirection.Input);
            OracleDataReader rdr = cmd.ExecuteReader();
            if (rdr.Read())
                deptno = (uint)rdr.GetInt32(0);
            rdr.Close();
            cmd.Dispose();
            conn.Close();
            return deptno;
        } // GetDeptNo
    } // CLRLibrary1

See Also: Oracle Database Extensions for .NET Developer's Guide for more information about how to create .NET Stored procedures Table 4-1, "API Support Comparison Between Client Application and .NET Stored Procedure"